<?php
	
class m_new_enrollee Extends MY_Model
{
	private $last_insert_id;
	private $CI;
	private $__table = 'enrollments';
	private $__seftable = 'student_enrollment_fees';
	
	public function __construct()
	{
		parent::__construct();
		$this->CI =& get_instance();
		$this->CI->load->model('m_level_fees','mlfees',true);
	}
	
	public function enroll_new_student($data)
	{
		$query_sy = $this->db->select(array('sy_from','sy_to'))->where('is_set','yes')->get('school_years');
		$sy = $query_sy->num_rows() > 0 ? $query_sy->result() : FALSE;
		
		if($sy !== FALSE)
		{
			foreach($sy as $sy){
				$data['e_sy_from'] = $sy->sy_from;
				$data['e_sy_to'] = $sy->sy_to;
			}
		}else{
			$data['e_sy_from'] = '007';
			$data['e_sy_to'] = '007';
		}
	
	
		$data['e_created_at'] = NOW;
		$this->db->insert($this->__table,$data);
		$this->last_insert_id = $this->db->insert_id();
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;	
	}
	
	/* old
	public function get_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy)
	{
		$data = $this->CI->mlfees->get_sel_fees($level_id);
		
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees){
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$count++;
			}
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			if($this->db->affected_rows() >= 1)
			{
				$this->set_tuition_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy);
			}
		}else
		{
			return TRUE;
		}
	}
	
	
	public function set_tuition_fees_set_for_current_level($data,$level_id,$enroll_id,$gperiod,$sy)
	{
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_tuition_fee'] = 1;
				$count++;
			}
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}
	}
	
	public function insert_misc_fee($data,$id,$sy)
	{
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_misc_fee'] = 1;
				$count++;
			}
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		
		}
	}
	
	public function _insert_cca_fee($id,$level_id,$fornew)
	{
		
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_cca_fee = 1
				AND f.grade_level = ?
				AND f.fee_stat = 'active' ";
			
		if($fornew=='yes')
		{
			$sql .= " AND f.fee_new = 'yes'";
		}else{
			$sql .= " AND f.fee_old = 'yes'";
		}	
				
		$query = $this->db->query($sql,array($level_id,));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		if(!empty($data))
		{
			foreach($data as $k => $fees){
				$s_e_fees[$k]['sef_enrollment_id'] = $id;
				$s_e_fees[$k]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$k]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$k]['sef_schoolyear_id'] = $this->c_sy->id;
				$s_e_fees[$k]['sef_created'] = NOW;
				$s_e_fees[$k]['is_cca_fee'] = 1;
			}
			
			$this->db->insert_batch('student_enrollment_fees',$s_e_fees);
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}else{
			return FALSE;
		}
	}
	
	public function get_last_input_id()
	{
		return $this->last_insert_id;
	}
	*/
	
	/*******************
	get applicant
	********************/
	public function get_applicant($e_id)
	{
		$sql = "SELECT e.e_grade_level,
									 e.guidance_recommend_level_id,
									 e.e_ay_id,
									 e.e_profile_id,
									 p.firstname,
									 p.middlename,
									 p.lastname,
									 p.name_ext,
									 p.dob,
									 p.contact_no,
									 p.previous_school
									 FROM enrollments e
									 LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
									 LEFT JOIN levels l ON e.e_grade_level = l.level_id
									 WHERE e.e_id = ?";
		$query = $this->db->query($sql,array($e_id));
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	/*
		Get all other fees assigned for a grade level then insert to student enrollment fees
		this is important so that if a fee is changed, the student will not be affected
		because they have their own record of the previous fees assigned to them
	*/
	public function get_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy,$e_stat)
	{
		//get all selected fees assigned for grade level
		//$data = $this->CI->mlfees->get_sel_fees($level_id);
		
		//if(!empty($data))
		//{
			//$count = 0;
			//foreach($data as $fees)
			//{
				//$s_e_fees[$count]['sef_enrollment_id'] = $enroll_id;
				//$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				//$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				//$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				//$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				//$s_e_fees[$count]['sef_created'] = NOW;
				//$s_e_fees[$count]['is_tuition_fee'] = $fees->is_tuition_fee;
				// $s_e_fees[$count]['is_cca_fee'] = $fees->is_cca_fee;
				// $s_e_fees[$count]['is_misc_fee'] = $fees->is_misc_fee;
				// $count++;
			//}
			// $this->db->insert_batch($this->__seftable,$s_e_fees);
			// if($this->db->affected_rows() >= 1)
			// {
			$q = $this->db->select('id')
					  ->where('payment_mode_name','cash basis')
					  ->get('payment_modes');
			$payment_plan_id = $q->num_rows() >= 1 ? $q->row() : FALSE; 

			$data_schedule = array(
							 array(
							'sps_e_id' => $enroll_id,
							'sps_pm_id' => $payment_plan_id->id,
							'sps_month' => 'Registration',
							'sps_tuition' => 0.00,
							'sps_matri' => 0.00
							)
							);

			$this->db->delete('student_payment_schedules', array('sps_e_id' => $enroll_id)); 
			$this->db->insert_batch('student_payment_schedules', $data_schedule); 
			
			/*
			if($e_stat == 'new'){
			$sql = "SELECT f.fee_name,f.fee_rate
					FROM fees f 
					WHERE f.is_assess_fee = 1
					AND f.ay_id = ?
					AND f.fee_stat = 'active'
					";
			$query = $this->db->query($sql,array($sy));
			$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
			
			if(!empty($data)){
			//format assessment fees for entry
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_other_fee'] = 1;
				$count++;
			}
			}


			//insert assessment fees
			$this->db->insert_batch('student_enrollment_fees',$s_e_fees);
			}
			*/

				//set tuition fee and misc fee for the current level
				if($this->set_tuition_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy,$e_stat) == TRUE)
				{
					//$this->_check_enrollee_nationality($level_id,$enroll_id);
					return TRUE;
				}else{
					log_message('error','unable to set fees for current level id '.$enroll_id);
					return FALSE;
				}
			// }
		// }
		//else
		//{
			//return TRUE;
		//}
	}
	
	private function _check_enrollee_nationality($lid,$eid)
	{
		// get enrolee nationality
		$q = $this->db->select('nationality')
				 ->join('enrollments e','e.e_profile_id = p.profile_id')
				 ->where('e_id',$eid)
				 ->limit(1)
				 ->get('profiles p');
		
		
		if($q->num_rows() >=1 )
		{
			$nat= $q->row()->nationality;		
				
			//if nationality is filipino no foreign fee applied
			if(strtolower($nat) == 'filipino')
			{
				return TRUE;
			}else{
				//get and insert foreign fees
				$ff = 'INSERT INTO student_enrollment_fees (sef_enrollment_id,
															sef_fee_name,
															sef_fee_rate,
															sef_gperiod_id,
															sef_schoolyear_id,
															is_other_fee
															)
						SELECT ?,ff.fee_name,ff.fee_rate,?,?,1
						FROM foreign_fee ff
						WHERE ff.level_id = ?';
				
				$this->db->query($ff,array($eid,$this->c_gp->gp_id,$this->c_sy->id,$lid));
				
				return TRUE;
			}
		}else{
			return TRUE;
		}
	}
	
	/*
		function that inserts tuition to enrollee
	*/
	public function set_tuition_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy,$e_stat)
	{
		/*
			Selects all tuition fee that is active.
		*/
		
		$c_spr_id = $this->M_student_payment_record->current_payment_record_id($enroll_id);
		
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_tuition_fee = 1
				AND f.grade_level = ?
				AND f.ay_id = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}	
		$query = $this->db->query($sql,array($level_id,$sy));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		//format tuition fees for entry
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_tuition_fee'] = 1;
				
				if($c_spr_id !== NULL)
				{
					$for_pb[$counter]['spb_fee_name'] = $fees->fee_name;
					$for_pb[$counter]['spb_remaining_balance'] = $fees->fee_rate;
					$for_pb[$counter]['spb_fee_rate'] = $fees->fee_rate;
					$for_pb[$counter]['spb_transacted_by'] =$this->CI->session->userdata('userid');
					$for_pb[$counter]['spb_spr_id'] =$c_spr_id;
					$for_pb[$counter]['spb_created'] =NOW;
					$for_pb[$counter]['spb_stud_enrollment_id'] = $enroll_id;
					$for_pb[$counter]['is_tuition_fee'] = 1;
				}
				
				$count++;
			}
			foreach($data as $fees){
			$data_tuit['sps_tuition'] = $fees->fee_rate;
			}
			$this->db->where('sps_e_id', $enroll_id);
			$this->db->update('student_payment_schedules', $data_tuit);

			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			if(isset($for_pb) && !empty($for_pb)){
			$this->db->insert_batch($this->__spbtable,$for_pb);
			}
			/*
				if successfully inserted tuition fee
				call method insert_miscellaneous_fee()
				this method will insert misc fee for the student
			*/
			if($this->db->affected_rows() >= 1 )
			{
				//call method to insert misc fee
				if($this->_insert_miscellaneous_fee($enroll_id,$sy,$level_id,$e_stat) == TRUE)
				{
					return TRUE;
				}else{
					log_message('error','Unable to insert Miscellaneous fees. ID'.$enroll_id);
					return TRUE;
				}
			}else{
				log_message('error','Unable to insert Tuition fees. ID'.$enroll_id);
				return TRUE;
			}
		}else{
			log_message('error','No data for Tuition fees yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	/*
		Inserts misc fees to students, called after inserting the tuition fee,
		at method set_tuition_fees_set_for_current_level();
	*/
	public function _insert_miscellaneous_fee($enroll_id,$sy,$level_id,$e_stat)
	{
		$c_spr_id = $this->M_student_payment_record->current_payment_record_id($enroll_id);
		
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_misc_fee = 1
				AND f.grade_level = ?
				AND f.ay_id = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}	
				
		$query = $this->db->query($sql,array($level_id,$sy));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees){
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_misc_fee'] = 1;
				
				if($c_spr_id !== NULL)
				{
					$for_pb[$counter]['spb_fee_name'] = $fees->fee_name;
					$for_pb[$counter]['spb_remaining_balance'] = $fees->fee_rate;
					$for_pb[$counter]['spb_fee_rate'] = $fees->fee_rate;
					$for_pb[$counter]['spb_transacted_by'] =$this->CI->session->userdata('userid');
					$for_pb[$counter]['spb_spr_id'] =$c_spr_id;
					$for_pb[$counter]['spb_created'] =NOW;
					$for_pb[$counter]['spb_stud_enrollment_id'] = $enroll_id;
					$for_pb[$counter]['is_misc_fee'] = 1;
				}
				
				$count++;
			}
			
			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			if(isset($for_pb) && !empty($for_pb)){
			$this->db->insert_batch($this->__spbtable,$for_pb);
			}
			
			if($this->db->affected_rows() >= 1)
			{
				//call method to insert misc fee
				if($this->_insert_matriculation_fee($enroll_id,$sy,$level_id,$e_stat) == TRUE)
				{
					return TRUE;
				}else{
					log_message('error','Unable to insert Matriculation fees. ID'.$enroll_id);
					return TRUE;
				}
			}else{
				log_message('error','Unable to insert Miscellaneous fees. ID'.$enroll_id);
				return TRUE;
			}

		}else{
			log_message('error','No data Miscellaneous fees yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	/*
		Inserts matriculation fees to students, called after inserting the miscellaneous fee,
		at method set_tuition_fees_set_for_current_level();
	*/
	public function _insert_matriculation_fee($enroll_id,$sy,$level_id,$e_stat)
	{
		$c_spr_id = $this->M_student_payment_record->current_payment_record_id($enroll_id);
		
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_cca_fee = 1
				AND f.grade_level = ?
				AND f.ay_id = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}	
		$query = $this->db->query($sql,array($level_id,$sy));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees){
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_cca_fee'] = 1;
				
				if($c_spr_id !== NULL)
				{
					$for_pb[$counter]['spb_fee_name'] = $fees->fee_name;
					$for_pb[$counter]['spb_remaining_balance'] = $fees->fee_rate;
					$for_pb[$counter]['spb_fee_rate'] = $fees->fee_rate;
					$for_pb[$counter]['spb_transacted_by'] =$this->CI->session->userdata('userid');
					$for_pb[$counter]['spb_spr_id'] =$c_spr_id;
					$for_pb[$counter]['spb_created'] =NOW;
					$for_pb[$counter]['spb_stud_enrollment_id'] = $enroll_id;
					$for_pb[$counter]['is_cca_fee'] = 1;
				}
				
				$count++;
			}
			
			foreach($data as $fees){
			$data_matri['sps_matri'] = $fees->fee_rate;
			}
			$this->db->where('sps_e_id', $enroll_id);
			$this->db->update('student_payment_schedules', $data_matri);

			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			if(isset($for_pb) && !empty($for_pb)){
			$this->db->insert_batch($this->__spbtable,$for_pb);
			}
			
			if($this->db->affected_rows() >= 1)
			{
				return TRUE;
			}else{
				log_message('error','Unable to insert Matriculation fees. ID'.$enroll_id);
				return TRUE;
			}

		}else{
			log_message('error','No data Matriculation fees yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	public function get_fees_set_for_foreign($level_id,$enroll_id,$gperiod,$sy,$e_stat)
	{
		/*
			Select foreign fee.
		*/
		
		$sql = "SELECT fee_name,fee_rate
				FROM foreign_fee 
				WHERE level_id = ?
				";
		$query = $this->db->query($sql,array($level_id,));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		//format foreign fee for entry
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_other_fee'] = 1;
				$count++;
			}
			//insert foreign fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			/*
				if successfully inserted foreign fee
				return true else false
			*/
			if($this->db->affected_rows() >= 1 )
			{
				return TRUE;
			}else{
				log_message('error','Unable to insert Foreign fees. ID'.$enroll_id);
				return TRUE;
			}
		}else{
			log_message('error','No data for Foreign fee yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	public function get_set_foreign_fee($enroll_id,$gperiod,$sy)
	{
		/*
			Select foreign fee.
		*/
		
		$sql = "SELECT fee_name,fee_rate
				FROM foreign_fee
				";
		$query = $this->db->query($sql);
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		//format foreign fee for entry
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_other_fee'] = 1;
				$count++;
			}
			//insert foreign fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			/*
				if successfully inserted foreign fee
				return true else false
			*/
			if($this->db->affected_rows() >= 1 )
			{
				return TRUE;
			}else{
				log_message('error','Unable to insert Foreign fees. ID'.$enroll_id);
				return TRUE;
			}
		}else{
			log_message('error','No data for Foreign fee yet. ID'.$enroll_id);
			return TRUE;
		}
	}
}